﻿using System;
using System.Data;
using System.Collections;
using System.Collections.Specialized;
using WP.DB;
using WP.RM.DataEntity;
using WP.Commons;


namespace WP.RM.DataAccess
{

   
    public class rmWorkQtyInputQryDAL
    {
        public struct RptPara
        {
            public DBAccess dbcon;
            public string strCondition;
        }

        /// <summary>
        /// 考勤录入查询
        /// </summary>
        /// <param name="rptPara"></param>
        /// <returns></returns>
        public bool GetData(RptPara rptPara)
        {
            DataFunctions.DelTemporyTable(rptPara.dbcon, "#tmpDetail"); 
            string strSQL = @"Select 
                        Hdr.WorkShopSeqNo,WKShop.WorkShopName,
                        Hdr.WorkDate,
                        Hdr.WorkingProcedureID,WKProcedure.WorkingProcedureName,
                        Hdr.MachineSeqNo,Machine.MachineName,
                        Hdr.WorkGroupSeqNo,WKGroup.WorkGroupName,
                        Dtl.WorkerID,Worker.WorkerName,
                        Hdr.ProductFItemID,Hdr.ProductName,
                        Hdr.SemiCode,SemiProduct.SemiName,
                        Dtl.WKPriceType,Dtl.UnitCost,Dtl.Qty,Hdr.Unit,
                        Dtl.UnitCost*Dtl.Qty as Total
                        into #tmpDetail From dbo.rmWorkQtyInputHdr Hdr 
                        inner join dbo.rmWorkShop WKShop on Hdr.WorkShopSeqNo=WKShop.WorkShopSeqNo
                        inner join dbo.rmWorkQtyInputDtl Dtl on Hdr.RecID=Dtl.HdrRecID
                        inner join dbo.rmWorker Worker On Worker.WorkerID=Dtl.WorkerID
                        inner join dbo.rmWorkingProcedure WKProcedure on WKProcedure.WorkingProcedureID=Hdr.WorkingProcedureID
                        inner join dbo.rmMachine Machine on Machine.MachineSeqNo=Hdr.MachineSeqNo
                        inner join dbo.rmWorkGroup WKGroup on WKGroup.SeqNo=Hdr.WorkGroupSeqNo
                        left join dbo.imSemiProduct SemiProduct on Hdr.SemiCode=SemiProduct.SemiCode
                        {0}
                        ";
            strSQL = string.Format(strSQL, rptPara.strCondition);
            rptPara.dbcon.ExecuteSql(strSQL); 
            return true;
        }

        /// <summary>
        /// 产量录入查询
        /// </summary>
        /// <param name="rptPara"></param>
        /// <returns></returns>
        public bool GetData_PruductQry(RptPara rptPara)
        {
            DataFunctions.DelTemporyTable(rptPara.dbcon, "#tmpDetail");
            string strSQL = @"Select 
                        Hdr.WorkShopSeqNo,WKShop.WorkShopName,
                        Hdr.WorkDate,
                        Hdr.WorkingProcedureID,WKProcedure.WorkingProcedureName,
                        Hdr.MachineSeqNo,Machine.MachineName,
                        Hdr.WorkGroupSeqNo,WKGroup.WorkGroupName,                        
                        Hdr.ProductFItemID,Hdr.ProductName,
                        Hdr.SemiCode,SemiProduct.SemiName,
                        Hdr.WorkQty,Hdr.Unit
                        into #tmpDetail From dbo.rmWorkQtyInputHdr Hdr 
                        inner join dbo.rmWorkShop WKShop on Hdr.WorkShopSeqNo=WKShop.WorkShopSeqNo
                        inner join dbo.rmWorkQtyInputDtl Dtl on Hdr.RecID=Dtl.HdrRecID
                        inner join dbo.rmWorker Worker On Worker.WorkerID=Dtl.WorkerID
                        inner join dbo.rmWorkingProcedure WKProcedure on WKProcedure.WorkingProcedureID=Hdr.WorkingProcedureID
                        inner join dbo.rmMachine Machine on Machine.MachineSeqNo=Hdr.MachineSeqNo
                        inner join dbo.rmWorkGroup WKGroup on WKGroup.SeqNo=Hdr.WorkGroupSeqNo
                        left join dbo.imSemiProduct SemiProduct on Hdr.SemiCode=SemiProduct.SemiCode
                        {0}
                        ";
            strSQL = string.Format(strSQL, rptPara.strCondition);
            rptPara.dbcon.ExecuteSql(strSQL);
            return true;
        }



        public bool GetData_ByWorker(RptPara rptPara)
        {
            DataFunctions.DelTemporyTable(rptPara.dbcon, "#tmpHdr");
            DataFunctions.DelTemporyTable(rptPara.dbcon, "#tmpDetail");
            DataFunctions.DelTemporyTable(rptPara.dbcon, "#tmpSummary");
            string strSQL = @"select 
                            Worker.WorkShopSeqNo as WKShopSeqNo,WKShop.WorkShopName as WKShopName,
                            Hdr.WorkShopSeqNo,WKShop.WorkShopName,
                            Hdr.WorkDate,
                            Hdr.WorkingProcedureID,WKProcedure.WorkingProcedureName,
                            Hdr.MachineSeqNo,Machine.MachineName,
                            Hdr.WorkGroupSeqNo,WKGroup.WorkGroupName,  
                            Dtl.WorkerID,Worker.WorkerName,
                            Hdr.ProductFItemID,Hdr.ProductName,
                            Hdr.SemiCode,SemiProduct.SemiName,
                            Dtl.WKPriceType,Dtl.UnitCost,Dtl.Qty,Hdr.Unit,
                            Dtl.UnitCost*Dtl.Qty as Total
                            into #tmpDetail From dbo.rmWorkQtyInputHdr Hdr 
                            inner join dbo.rmWorkShop WKShop on Hdr.WorkShopSeqNo=WKShop.WorkShopSeqNo 
                            inner join dbo.rmWorkQtyInputDtl Dtl on Hdr.RecID=Dtl.HdrRecID
                            inner join dbo.rmWorker Worker On Worker.WorkerID=Dtl.WorkerID and Worker.WorkShopSeqNo=WKShop.WorkShopSeqNo
                            inner join dbo.rmWorkingProcedure WKProcedure on WKProcedure.WorkingProcedureID=Hdr.WorkingProcedureID
                            inner join dbo.rmMachine Machine on Machine.MachineSeqNo=Hdr.MachineSeqNo
                            inner join dbo.rmWorkGroup WKGroup on WKGroup.SeqNo=Hdr.WorkGroupSeqNo
                            left join dbo.imSemiProduct SemiProduct on Hdr.SemiCode=SemiProduct.SemiCode
                        {0}
                        ";
            strSQL = string.Format(strSQL, rptPara.strCondition);
            rptPara.dbcon.ExecuteSql(strSQL);

            strSQL = @"select 
                        Worker.WorkShopSeqNo as WKShopSeqNo,Cast('' as varchar(100)) as  WKShopName,
                        Hdr.WorkShopSeqNo,WKShop.WorkShopName,
                        WorkDaysSalary,WorkDays,HolidayDays ,
                        Hdr.WorkerID,Worker.WorkerName
                        into #tmpHdr from rmWorkerSTDSalary Hdr
                        inner join dbo.rmWorkShop WKShop on Hdr.WorkShopSeqNo=WKShop.WorkShopSeqNo
                        inner join dbo.rmWorker Worker On Worker.WorkerID=Hdr.WorkerID
                        {0}
                        ";
            strSQL = string.Format(strSQL, rptPara.strCondition);
            rptPara.dbcon.ExecuteSql(strSQL);

            strSQL = "update #tmpHdr Set WKShopName=rmWorkShop.WorkShopName from rmWorkShop where #tmpHdr.WKShopSeqNo=rmWorkShop.WorkShopSeqNo";
            rptPara.dbcon.ExecuteSql(strSQL);


            strSQL = @"Select Hdr.WorkerId,Hdr.WorkerName, Hdr.WKShopSeqNo, Hdr.WKShopName,
                      sum(Dtl.Qty) as Qty, sum(Hdr.WorkDaysSalary) as WorkDaysSalary,
                      sum(Hdr.WorkDays) as WorkDays, sum(Hdr.HolidayDays) as HolidayDays,cast(0 as decimal(18,4)) as Total
                      into #tmpSummary  From #tmpHdr Hdr inner join #tmpDetail Dtl on Hdr.WorkerID=Dtl.WorkerID
                      GROUP BY Hdr.WorkerId,Hdr.WorkerName,Hdr.WKShopSeqNo, Hdr.WKShopName
                     ";
            rptPara.dbcon.ExecuteSql(strSQL);
            strSQL = @"Update #tmpSummary set Total=B.Total from ( select WorkerID, sum(Total) as Total from #tmpDetail Group By WorkerID) B 
                       Where #tmpSummary.WorkerID=B.WorkerID";
            rptPara.dbcon.ExecuteSql(strSQL);

            return true;
        }
    
    }
}
